package com.zhonghan.common.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
/**
 * ExcelUtils Excel工具类
 * 基于 Apache POI
 * @author zhangceven
 * 
 */
public class ExcelUtils {

	/**
	 * fileDir: 文件路径
	 */
	
	private String fileDir;
	
	/**
	 * fileName: 文件名
	 */
	private String fileName;
	
	/**
	 * sheetName: 工作表名
	 */
	private String sheetName;
	
	/**
	 * sheetNames: 工作表名(多个)
	 */
	private String[] sheetNames;
	
	/**
	 * workbook: 工作簿
	 */
	private HSSFWorkbook workbook;
	
	/**
	 * sheet:	工作表
	 */
	private HSSFSheet sheet;
	
	/**
	 * request 请求
	 */
	private HttpServletRequest request;
	
	/**
	 * response 响应
	 */
	private HttpServletResponse response;
	
	/**
	 * 数字类型格式化
	 */
	DecimalFormat decimalFormat = new DecimalFormat("######.000");
	
	/**
	 * 日期格式化
	 */
	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	/*** 空参构造方法 */
	public ExcelUtils(){
		
	}
	
	/**
	 * 带参构造: 单个工作表导出
	 * 使用HTTP请求响应以流文件导出
	 * @param request	请求
	 * @param response	响应
	 * @param fileName	文件名称
	 * @param sheetName	工作表名
	 */
	public ExcelUtils(HttpServletRequest request,HttpServletResponse response,String fileName,String sheetName){
		this.request = request;
		this.response = response;
		this.fileName = fileName;
		this.sheetName = sheetName;
		workbook = new HSSFWorkbook();
		sheet = workbook.createSheet(sheetName);
	}
	
	/**
	 * 带参构造: 单个工作表导出
	 * 指定目录导出文件
	 * @param fileDir	文件路径
	 * @param fileName	文件名称
	 * @param sheetName	工作表名
	 */
	public ExcelUtils(String fileDir,String fileName,String sheetName){
		this.fileDir = fileDir;
		this.fileName = fileName;
		this.sheetName = sheetName;
		workbook = new HSSFWorkbook();
		sheet = workbook.createSheet(sheetName);
	}
	
	/**
	 * 设置表格标题
	 * @param title		标题名称
	 * @param cellRangeAddressLength	跨列列数
	 */
	public void setTitle(String title, int cellRangeAddressLength){
		HSSFRow rowTitle = null;
		
		//判断第一行是否创建,如果为空则表示没有表头
		if(sheet.getRow(0) == null){
			rowTitle = sheet.createRow(0);
		//否则就添加一行
		}else{
			int lastRowNum = sheet.getLastRowNum();
			sheet.shiftRows(1, lastRowNum, 1); //向前插入一行 
			rowTitle = sheet.createRow(1);
			
		}
		
		HSSFCellStyle styleTitle = createCellStyle(workbook,(short)16); // 创建单元格格式,字号16
		sheet.setDefaultColumnWidth(25); // 设置默认列宽
		CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, cellRangeAddressLength);//设置单元格位置及长度
		sheet.addMergedRegion(cellRangeAddress); // 合并单元格
		
		//HSSFRow rowTitle = sheet.createRow(0); // 创建一行
		HSSFCell cellTitle = rowTitle.createCell(0); // 创建一个单元格
		
		
		// 为标题设置背景颜色
		styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		styleTitle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		cellTitle.setCellValue(title);
		cellTitle.setCellStyle(styleTitle);
	}
	
	/**
	 * 设置表头
	 * @param headers
	 * @param colNum
	 */
	public void setHeader(String[] headers){
       
		HSSFRow rowField;
		// 判断第一行是否创建,如果为空就表示没有标题
		if(sheet.getRow(0) == null){
			rowField = sheet.createRow(0);
		}else{
			rowField = sheet.createRow(1);
			
		}
		
        HSSFCellStyle styleField = createCellStyle(workbook, (short)13);        
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = rowField.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(styleField);            
        }
    }
	
	/**
	 * 导出Excel文件
	 * @param headerColumns	表头的字段
	 * @param dataList		数据集合
	 */
	public void export(String[] headerColumns,List<?> dataList){
		OutputStream os = null;
		fileName = fileName.concat(".xls");
		try {
			// 如果有文件路径
			if(StringUtils.isNotEmpty(fileDir)){
				os = new FileOutputStream(fileDir.concat(fileName));
			// 没有则用响应流导出
			}else{
				// 判断用户是什么浏览器
				String agent = request.getHeader("User-Agent");
				// 判断是否是火狐浏览器,如果是火狐浏览器导出会乱码,需要指定编码
				boolean isFirefox = agent != null && agent.contains("Firefox");
				if(isFirefox){
					fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
				}else{
					fileName = URLEncoder.encode(fileName,"UTF-8");
					//写入到输出流中
					os = response.getOutputStream();
					
					response.setContentType("application/x-msdownload");
					response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
				}
				
				//通过反射获取数据并写入到Excel中
				if(dataList != null && dataList.size()>0){
					
					if(headerColumns.length > 0){
						
						//第一行第二行已经创建标题了，所以从第三行开始，索引为2
						for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
							Object obj = dataList.get(rowIndex);//因为索引从2开始，要获取第一个对象要减2
							Class clazz = obj.getClass();
							Row dataRow = workbook.getSheet(sheetName).createRow(rowIndex+2);
							//填充数据到每一列
							for (int columnIndex = 0; columnIndex < headerColumns.length; columnIndex++) {
								String title = headerColumns[columnIndex].toString().trim();
								if(StringUtils.isNotEmpty(title)){
									//将传入字段的字母开头则设置为大写拼接为geter方法
									String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1, title.length());
									String methodName = "get" + UTitle;
									
									//设置执行的方法
									Method method = clazz.getDeclaredMethod(methodName);
									Object data = method.invoke(obj) == null ? "" : method.invoke(obj);//执行方法转成string类型
									
									//获取返回类型
									String returnType = method.getReturnType().getName();
									Cell cell = dataRow.createCell(columnIndex);
									//cell.setCellValue 该api只能设置boolean,String,double,Date,Calendar,RichTextString
									if("java.lang.Float".equals(returnType)){
										//cell.setCellValue(decimalFormat.format(Float.parseFloat(data)));
										cell.setCellValue(decimalFormat.format(data));
									}else if("java.lang.Double".equals(returnType)){
										//cell.setCellValue(decimalFormat.format(Float.parseFloat(data)));
										cell.setCellValue(decimalFormat.format(data));
									}else if("BigDecimal".equals(returnType)){
										cell.setCellValue(decimalFormat.format(data));
									}else if("java.util.Date".equals(returnType)){
										cell.setCellValue(dateFormat.format(data));
									}else{
										cell.setCellValue(data.toString());
									}
								}
							}
						}
					}
				}
				
				workbook.write(os);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	
	/**
     * 提取公共的样式
     * @param workbook	工作簿
     * @param fontSize	字体大小
     * @return
     */
	private HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
		HSSFCellStyle style = workbook.createCellStyle();        
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 创建一个字体样式
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(fontSize);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        return style;
	}
	
	
	/**
	 * 使用批量导入方法时，请注意需要导入的Bean的字段和excel的列一一对应
	 * @param clazz
	 * @param file
	 * @param beanPropertys
	 * @param rowIndex 导入起始行索引
	 * @return
	 */
    public static <T> List<T> excelParserList(Class<T> clazz, File file, String[] beanPropertys, int rowIndex) {
        // 得到workbook
        List<T> list = new ArrayList<T>();
        try {
            Workbook workbook = WorkbookFactory.create(file);
            Sheet sheet = workbook.getSheetAt(0);
            // 直接从第二行开始获取数据
            int rowSize = sheet.getPhysicalNumberOfRows();
            if(rowSize > 0){                
                for (int i = rowIndex; i < rowSize; i++) {
                    T t = clazz.newInstance();
                    Row row = sheet.getRow(i);
                    int cellSize = row.getPhysicalNumberOfCells();
                    for(int j=0; j<cellSize; j++){
                        
                        Object cellValue = getCellValue(row.getCell(j));
                        BeanUtils.copyProperty(t, beanPropertys[j], cellValue);
                        }                        
                                    
                    list.add(t);

                }
            }            

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;

    }
    
    
    /**
     * 通用的读取excel单元格的处理方法
     * @param cell
     * @return
     */
    private static Object getCellValue(Cell cell) {
        Object result = null;
        if (cell != null) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                result = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                //对日期进行判断和解析
                if(HSSFDateUtil.isCellDateFormatted(cell)){
                    double cellValue = cell.getNumericCellValue();
                    result = HSSFDateUtil.getJavaDate(cellValue);
                }
                
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                result = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                result = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_ERROR:
                result = cell.getErrorCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            default:
                break;
            }
        }
        return result;
    }
	
    /**
     * 判断文件是否是Excel文件
     * @param request
     * @param response
     * @return
     */
    public  boolean isExcel(HttpServletRequest request,HttpServletResponse response){  
        boolean isExcel = true;  
        String filePath = request.getParameter("filePath");
        String fileType = filePath.substring(filePath.lastIndexOf(".")+1);
        if(!"xlsx".equals(fileType) && !"xls".equals(fileType)){
            isExcel = false;  
        }  
        return isExcel;
    }
    
	
}
